package cc.mrbird.febs.accounting.service.impl;

import cc.mrbird.febs.accounting.Constant.CompanyConstant;
import cc.mrbird.febs.accounting.dto.detailCost.Salary;
import cc.mrbird.febs.accounting.dto.project.ProjectInfoDTO;
import cc.mrbird.febs.accounting.dto.detailCost.DetailCostDTO;
import cc.mrbird.febs.accounting.dto.detailCost.DetailCostParam;
import cc.mrbird.febs.accounting.dto.importAndExport.*;
import cc.mrbird.febs.accounting.dto.standardSub.ExportFlagDTO;
import cc.mrbird.febs.accounting.dto.standardSub.SingleExportDTO;
import cc.mrbird.febs.accounting.dto.standardSub.SubMainDetailDTO;
import cc.mrbird.febs.accounting.entity.*;
import cc.mrbird.febs.accounting.mapper.*;
import cc.mrbird.febs.accounting.service.*;
import cc.mrbird.febs.business.entity.Business;
import cc.mrbird.febs.business.mapper.BusinessMapper;
import cc.mrbird.febs.business.service.IBusinessService;
import cc.mrbird.febs.statistics.service.impl.DataExportServiceImpl;
import cc.mrbird.febs.utils.ArithUtil;
import cc.mrbird.febs.utils.CodeConstant;
import cc.mrbird.febs.utils.StyleMap;
import cc.mrbird.febs.utils.YearMonthMapUtils;
import cc.mrbird.febs.utils.*;
import cc.mrbird.febs.common.utils.FebsUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.sun.org.apache.bcel.internal.generic.NEW;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.annotation.Propagation;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import javax.annotation.Resource;
import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Service实现
 *
 * @author baoyinlei
 * @date 2021-09-28 23:27:01
 */
@Service
@Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
public class DetailCostServiceImpl extends ServiceImpl<DetailCostMapper, DetailCost> implements IDetailCostService {

    @Resource
    private IProjectService projectService;
    @Resource
    private CompanyServiceImpl companyService;
    @Resource
    private CompanyProjectServiceImpl companyProjectService;
    @Resource
    private DetailCostMapper detailCostMapper;
    @Resource
    private IDetailSubjectService detailSubjectService;
    @Resource
    private IStandardSubService standardSubService;
    @Resource
    private IStandardSubExtraService standardSubExtraService;
    @Resource
    private MainCostMapper mainCostMapper;
    @Resource
    private EstimateHistoryMapper estimateHistoryMapper;
    @Resource
    private StandardSubExtraMapper standardSubExtraMapper;
    @Resource
    private DataExportServiceImpl dataExportServiceImpl;
    @Resource
    private BusinessMapper businessMapper;
    @Resource
    private IBusinessService businessService;
    @Resource
    private DetailCostUtil detailCostUtil;

    @Override
    public List<DetailCost> findDetailCosts() {
        LambdaQueryWrapper<DetailCost> queryWrapper = new LambdaQueryWrapper<>();

        queryWrapper.eq(DetailCost::getIsDeleted, 0);
        return this.baseMapper.selectList(queryWrapper);
    }

    @Override
    public Map<String, Object> queryDetailCost(Long companyId, Long projectId, String yearMonth) {
        Map<String, Object> resultMap = new HashMap<>();
        Project project = this.projectService.getById(projectId);
        Company company = this.companyService.getById(companyId);
        resultMap.put("projectInfo", ProjectInfoDTO.builder().projectName(project.getProjectName())
                .companyName(company.getCompanyName()).yearMonth(yearMonth).manualTax(project.getManualTax()).build());
        List<DetailCostDTO> detailCostDTOS = this.queryDetailCostByYearMonth(companyId, projectId, yearMonth);
        List<StandardMain> standardMains = new ArrayList<>();
        Set<String> codes = new HashSet<>();

        detailCostDTOS.forEach(e -> {
            if (!codes.contains(e.getMainSubjectCode())) {
                codes.add(e.getMainSubjectCode());
                StandardMain standardMain = new StandardMain();
                standardMain.setCode(e.getMainSubjectCode());
                standardMain.setName(e.getMainSubjectName());
                standardMains.add(standardMain);
            }
        });
        detailCostDTOS.forEach(e -> standardMains.forEach(m -> {
            if (e.getMainSubjectCode().equals(m.getCode())) {
                List<DetailCostDTO> detailCostList = m.getDetailCostDTOS() == null ? new ArrayList<>() : m.getDetailCostDTOS();
                detailCostList.add(e);
                m.setDetailCostDTOS(detailCostList);
            }
        }));
        resultMap.put("standardMains", standardMains);
        return resultMap;
    }

    @Override
    public List<DetailCost> queryByType(Integer type) {
        LambdaQueryWrapper<DetailCost> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(DetailCost::getIsDeleted, 0);
        wrapper.eq(DetailCost::getType, type);
        return this.baseMapper.selectList(wrapper);
    }

    /////
    @Override
    @Transactional
    public void saveDetailCost(DetailCostParam detailCostParam) {
        // records: 把detailCost表里类型是4,5,6的对应的projectId筛选出来
        LambdaQueryWrapper<DetailCost> q1 = new LambdaQueryWrapper<>();
        q1.eq(DetailCost::getProjectId, detailCostParam.getProjectId());
        q1.eq(DetailCost::getCompanyId, detailCostParam.getCompanyId());
        q1.in(DetailCost::getType, new ArrayList<>(Arrays.asList("4", "5", "6")));
        List<DetailCost> records = this.baseMapper.selectList(q1);
//      countMap: 每一个detailSubjectCode在records中出现的次数
        Map<String, Integer> countMap = new HashMap<>();
        for (DetailCost record : records) {
            countMap.put(record.getDetailSubjectCode(), countMap.getOrDefault(record.getDetailSubjectCode(), 0) + 1);
        }

        // 删除对应记录后重新新增？？为什么要删除
        LambdaQueryWrapper<DetailCost> deleteWrapper = new LambdaQueryWrapper<>();

        deleteWrapper.eq(DetailCost::getProjectId, detailCostParam.getProjectId());
        deleteWrapper.eq(DetailCost::getCompanyId, detailCostParam.getCompanyId());
        deleteWrapper.eq(DetailCost::getYearMonth, detailCostParam.getYearMonth());
        this.remove(deleteWrapper);

        // idMap: business那张表所对应的code -> id;
        LambdaQueryWrapper<Business> q3 = new LambdaQueryWrapper<>();
        List<Business> businessList = businessMapper.selectList(q3);
        Map<String, Long> idMap = new HashMap<>();
        for (Business business : businessList) {
            idMap.put(business.getCode(), business.getId());
        }
//      把数据从standSubExtra这张表里删了，为什么只删value=1的？
        for (DetailCost record : records) {
            if (record.getYearMonth().equals(detailCostParam.getYearMonth())) {
                Integer value = countMap.get(record.getDetailSubjectCode());
                // 如果value是1，detailSubjectCode在records中只出现过一次
                if (value == 1) {
                    LambdaQueryWrapper<StandardSubExtra> del = new LambdaQueryWrapper<>();
                    del.eq(StandardSubExtra::getProjectId, detailCostParam.getProjectId())
                            .eq(StandardSubExtra::getBusinessId, idMap.get(record.getDetailSubjectCode()));
                    standardSubExtraService.remove(del);
                    countMap.put(record.getDetailSubjectCode(), 0);
                }
            }
        }
        List<DetailCost> addList = new ArrayList<>();
        List<StandardSubExtra> subAddList = new ArrayList<>();
        if (Objects.nonNull(detailCostParam.getDetailCostList()) && !detailCostParam.getDetailCostList().isEmpty()) {
            Map<String, DetailCost> map = new HashMap<>();
            // 合并同项
            for (DetailCost detailCost : detailCostParam.getDetailCostList()) {
                // 避免添加脏数据
                if ((detailCost.getType() > 3 && !idMap.containsKey(detailCost.getStandardSubCode()))
                        || detailCost.getStandardSubCode() == null) {
                    continue;
                }
                if (!map.containsKey(detailCost.getStandardSubCode())) {
                    map.put(detailCost.getStandardSubCode(), detailCost);
                } else {
                    DetailCost d = map.get(detailCost.getStandardSubCode());
                    detailCost.setCost(detailCost.getCost().add(d.getCost()));
                    map.put(detailCost.getStandardSubCode(), detailCost);
                }
            }
//            往detailCost和standardSubExtra表里添加数据，e是入参传过来的list里的detailcost
            for (DetailCost e : map.values()) {
                if (e.getType() == 4 || e.getType() == 5) {
//                    countMap在上面没有进行过被删的所对应的数据保存在额外表里？？？
                    if (countMap.getOrDefault(e.getStandardSubCode(), 0) < 1) {
                        StandardSubExtra standardSubExtra = new StandardSubExtra();
                        standardSubExtra.setProjectId(detailCostParam.getProjectId());
                        standardSubExtra.setBusinessId(idMap.get(e.getStandardSubCode()));
                        standardSubExtra.setCreateTime(new Date());
                        standardSubExtra.setCreateBy(FebsUtil.getCurrentUser().getUsername());
                        subAddList.add(standardSubExtra);
                    }
                }
                DetailCost detailCost = new DetailCost();
                detailCost.setType(e.getType());
                detailCost.setDetailSubjectCode(e.getStandardSubCode());
                detailCost.setCompanyId(detailCostParam.getCompanyId());
                detailCost.setProjectId(detailCostParam.getProjectId());
                detailCost.setCost(e.getCost());
                detailCost.setInputTaxAdjustment(e.getInputTaxAdjustment());
                detailCost.setYearMonth(detailCostParam.getYearMonth());
                detailCost.setIsDeleted(0);
                detailCost.setRemark(e.getRemark());
                detailCost.setCreateTime(new Date());
                detailCost.setEstimate(e.getEstimate());
                detailCost.setCreateBy(FebsUtil.getCurrentUser().getUsername());

                addList.add(detailCost);
            }
        }
        this.saveBatch(addList);
        this.standardSubExtraService.saveBatch(subAddList);
    }

    /////
    @Override
    public List<DetailCost> queryByProjectsAndYearMonths(List<String> projectIds, List<String> yearMonths, String ifEstimate) {
        LambdaQueryWrapper<DetailCost> wrapper = new LambdaQueryWrapper<>();
        if (Integer.parseInt(ifEstimate) >= 0) {
            wrapper.eq(DetailCost::getEstimate, ifEstimate);
        }
        wrapper.eq(DetailCost::getIsDeleted, 0);
        wrapper.in(DetailCost::getProjectId, projectIds);
        wrapper.in(DetailCost::getYearMonth, yearMonths);
        return this.baseMapper.selectList(wrapper);
    }

    @Override
    public List<DetailCostDTO> queryDetailCostByYearMonth(Long companyId, Long projectId, String yearMonth) {
        return this.baseMapper.queryDetailCostByYearMonth(companyId, projectId, yearMonth);
    }

    @Override
    public ExportFlagDTO queryDetailForExcel(Long companyId, Long projectId, String year, List<String> yearMonths) {

        ExportFlagDTO exportFlagDTO = new ExportFlagDTO();
        // 通过项目id和时间 获取detailCost表所有的数据信息
        List<DetailCost> detailCosts = this.baseMapper.queryDetailCodeByProjectIdAndYear(companyId, projectId, yearMonths);
        exportFlagDTO.setInsuranceError(CheckUtil.insuranceError(detailCosts));
        // 通过项目id和时间 获取所有的科目信息 ：标准科目(bk_standard_sub) + 自定义科目（bk_standard_sub_extra）
        List<SubMainDetailDTO> subMainDetailDTOS = this.standardSubService.queryAllSubDetail(companyId, projectId, yearMonths);
        // 将所有科目放入map中供后面调用，key为科目code
        Map<String, SubMainDetailDTO> subMainDetailDTOMap = new HashMap<>();
        List<String> typeFourNames = new ArrayList<>();
        List<String> typeSixNames = new ArrayList<>();
        int flag = 0;
        for (SubMainDetailDTO e : subMainDetailDTOS) {
            // containsKey()检测数据科目code（e.getSubCode()）是否真实存在，然后把它放入map内
            if (!subMainDetailDTOMap.containsKey(e.getSubCode())) {
                subMainDetailDTOMap.put(e.getSubCode(), e);
            }
            if (e.getType() == 4) {
                String name = e.getSubName().replace("-", "");
//                name = name.replaceAll("（.*）","");
                typeFourNames.add(name);
            }
            if (e.getType() == 6 && e.getCost() != 0.0) {
                String name = e.getSubName();
                if (name.contains("第三方-"))
                    name = e.getSubName().split("第三方-")[1].replace("-", "");
//                name = name.replaceAll("（.*）","");
                typeSixNames.add(name);
            }
        }
        for (String typeSixName : typeSixNames) {
            if (!typeFourNames.contains(typeSixName)) {
                flag = 1;
                break;
            }
        }
        for (String typeFourName : typeFourNames) {
            if (!typeSixNames.contains(typeFourName)) {
                flag = 1;
                break;
            }
        }
        exportFlagDTO.setFlag(flag);
        // Map<String, SubMainDetailDTO> subMainDetailDTOMap = subMainDetailDTOS.stream().collect(Collectors.toMap(SubMainDetailDTO::, Function.identity()));
        List<SingleExportDTO> singleExportDTOS = new ArrayList<>();
        LambdaQueryWrapper<Business> wrapper = new LambdaQueryWrapper<>();
        List<Business> businessList = businessMapper.selectList(wrapper);
        Map<String, String> map = TreeUtils.getFullNameByCode(businessList);
        // 存放两个标准的批注（来源于导入），存入到map供后面对应科目使用
        Map<String, String> remarkMap = new HashMap<>();
        Map<String, Double> codeValueMap = new HashMap<>();
        // 将所有detailCosts放入map中供后面计算第三类科目（计算科目）的金额,key为code==时间, value为转化为double类型的费用开支
        detailCosts.forEach(e -> {
            String key = e.getDetailSubjectCode().concat("==").concat(e.getYearMonth());
            codeValueMap.put(key, e.getCost().doubleValue());
            if (e.getDetailSubjectCode().equals(CodeConstant.yearEndAccrualStandardCode) || e.getDetailSubjectCode().equals(CodeConstant.otherWelfareStandardCode)) {
                key = e.getDetailSubjectCode().equals(CodeConstant.yearEndAccrualStandardCode) ? CodeConstant.annualBonus : CodeConstant.otherWelfare;
                key = key.concat("==").concat(e.getYearMonth());
                remarkMap.put(key, e.getRemark());
            }
        });
        // 遍历detailCosts, 放入自己的数据封装SingleExportDTO
        for (DetailCost e : detailCosts) {
            SingleExportDTO singleExportDTO = new SingleExportDTO();
            singleExportDTO.setCost(e.getCost());
            singleExportDTO.setEstimate(e.getEstimate());
            singleExportDTO.setRemark(e.getRemark());
            singleExportDTO.setYearMonth(e.getYearMonth());
            singleExportDTO.setSubCode(e.getDetailSubjectCode());
            // 从detailCost中获取到详细科目的id，根据这个id去详细科目的表里获取到科目id所对应的所有数据，保存到subMainDetailDTO中
            SubMainDetailDTO subMainDetailDTO = subMainDetailDTOMap.get(e.getDetailSubjectCode());
            String preStr = "";
            //  添加前缀
            if (e.getType() == 4) {
                preStr = "第三方-";
            } else if (e.getType() == 5) {
                preStr = "自有收入-";
            }
            singleExportDTO.setSubName(preStr + map.getOrDefault(e.getDetailSubjectCode(), subMainDetailDTO.getSubName()));
            singleExportDTO.setMainCode(subMainDetailDTO.getMainCode());
            singleExportDTO.setMainName(subMainDetailDTO.getMainName());
            singleExportDTOS.add(singleExportDTO);
        }
        // 在standardSub里获取所有的第三类科目
        List<StandardSub> standardSubs = this.standardSubService.queryByType(3);
        ScriptEngine jse = new ScriptEngineManager().getEngineByName("JavaScript");      // 计算工具
        // 第三类明细计算,通过将公式(科目表里的calculate_rule)里的code替换成真实金额，在通过ScriptEngine实例计算
        for (StandardSub standardSub : standardSubs) {
            String code = standardSub.getCode();
            // subMainDetailDTOMap内有所有的科目，在所有的科目中查找类型为3的code
            SubMainDetailDTO subMainDetailDTO = subMainDetailDTOMap.get(code);
            // 获取到计算规则用rule接收
            String rule = standardSub.getCalculateRule();
            // getRuleCodeSet返回一个装有规则的集合
            Set<String> ruleCodeSet = getRuleCodeSet(rule);
            for (String month : YearMonthMapUtils.monthList) {
                Double reuslt = 0d;
                String yearMonth = year + "-" + month;
                // 判断类别，为 4 ,5 类直接累加计算, 否则通过存入公式计算
                if (subMainDetailDTO.getSubName().equals("第三方收入合计")) {
                    for (DetailCost e : detailCosts) {
                        if (e.getYearMonth().equals(yearMonth) && e.getType() == 4) {
                            reuslt += Double.parseDouble(e.getCost().toString());
                        }
                    }
                } else if (subMainDetailDTO.getSubName().equals("自有收入合计")) {
                    for (DetailCost e : detailCosts) {
                        if (e.getYearMonth().equals(yearMonth) && e.getType() == 5) {
                            reuslt += Double.parseDouble(e.getCost().toString());
                        }
                    }
                } else if (subMainDetailDTO.getSubName().equals("第三方支出合计")) {
                    String key = CodeConstant.pendingPaymentCode;
                    for (DetailCost e : detailCosts) {
                        if (e.getYearMonth().equals(yearMonth) && (e.getType() == 6 || e.getDetailSubjectCode().equals(key))) {
                            reuslt += Double.parseDouble(e.getCost().toString());
                        }
                    }
                } else if (subMainDetailDTO.getSubName().equals(CompanyConstant.companyName + "项目收入")) {
                    // 加上自有收入合计和第三方收入合计
                    String key = CodeConstant.selfIncomeCode + "==" + yearMonth;
                    reuslt += codeValueMap.get(key);
                    key = CodeConstant.thirdPartCode + "==" + yearMonth;
                    reuslt += codeValueMap.get(key);
                } else {
                    // 通过公式计算
                    String standardSubKey = standardSub.getCode() + "==" + year + "-" + month;
                    if (codeValueMap.containsKey(standardSubKey)) {
                        continue;
                    }
                    String realRule = rule;
                    for (String ruleCode : ruleCodeSet) {
                        String key = ruleCode + "==" + year + "-" + month;
                        Double value = codeValueMap.get(key);
                        if (Objects.isNull(value)) {
                            value = 0d;
                        }
                        realRule = realRule.replaceAll("【" + ruleCode + "】", value.toString());
                    }
                    //计算公式
                    try {
                        reuslt = (Double) jse.eval(realRule);
                    } catch (ScriptException e) {
                        e.printStackTrace();
                    }
                    if (reuslt.isNaN() || reuslt.isInfinite()) {
                        reuslt = 0d;
                    }
                }
                reuslt = Double.parseDouble(String.format("%.2f", reuslt));
                SingleExportDTO singleExportDTO = new SingleExportDTO();
                singleExportDTO.setCost(new BigDecimal(reuslt));
                singleExportDTO.setYearMonth(year + "-" + month);
                singleExportDTO.setSubName(subMainDetailDTO.getSubName());
                singleExportDTO.setSubCode(subMainDetailDTO.getSubCode());
                singleExportDTO.setMainName(subMainDetailDTO.getMainName());
                singleExportDTO.setMainCode(subMainDetailDTO.getMainCode());
                singleExportDTOS.add(singleExportDTO);
                // 若两个有标准的科目无备注，则设为对应标准导入时的备注
                if (subMainDetailDTO.getSubCode().equals(CodeConstant.annualBonus) || subMainDetailDTO.getSubCode().equals(CodeConstant.otherWelfare))
                    singleExportDTO.setRemark(remarkMap.get(subMainDetailDTO.getSubCode().concat("==").concat(year + "-" + month)));
                codeValueMap.put(code + "==" + year + "-" + month, reuslt);
            }
        }
        exportFlagDTO.setSingleExportDTOS(singleExportDTOS);
        return exportFlagDTO;
    }

    @Override
    public void fillingField(List<SingleExportDTO> singleExportDTOS, Long projectId, List<String> yearMonths) {
        Set<String> codeSet = new HashSet<>();
        singleExportDTOS.forEach(e -> codeSet.add(e.getSubCode()));
        List<SingleExportDTO> selectSingleExportDTOS = this.standardSubService.querySubMainStandard(projectId, yearMonths);
        Map<String, SingleExportDTO> collect = new HashMap<>();
        Integer i = 1;
        for (SingleExportDTO e : selectSingleExportDTOS) {
            if (!collect.containsKey(e.getSubCode())) {
                collect.put(e.getSubCode(), e);
            }
        }
        // Map<String, SingleExportDTO> collect = selectSingleExportDTOS.stream().collect(Collectors.toMap(SingleExportDTO::getSubCode, Function.identity()));
        for (SingleExportDTO singleExportDTO : selectSingleExportDTOS) {
            if (!codeSet.contains(singleExportDTO.getSubCode())) {
                singleExportDTOS.add(singleExportDTO);
            }
        }
        for (SingleExportDTO singleExportDTO : singleExportDTOS) {
            SingleExportDTO singleExportDTO1 = collect.get(singleExportDTO.getSubCode());
            singleExportDTO.setOrder(singleExportDTO1.getOrder());
            singleExportDTO.setMainOrder(singleExportDTO1.getMainOrder());
        }
        singleExportDTOS.sort(Comparator.comparing(SingleExportDTO::getOrder));
    }

    @Override
    public List<SingleExportDTO> querySubjectDetail() {
        return this.baseMapper.querySubjectDetail();
    }

    private Set<String> getRuleCodeSet(String rule) {
        Set<String> codeSet = new HashSet<>();
        String pattern = "(?<=【)([^【】]*)(?=】)";
        Pattern r = Pattern.compile(pattern);
        Matcher m = r.matcher(rule);
        while (m.find()) {
            codeSet.add(m.group());
        }
        return codeSet;
    }


    ////
    @Override
    public ImportExcelData importExcel(InputStream inputStream, ImportParam importParam) throws IOException {
        ImportExcelData importExcelData = new ImportExcelData();
        if (CodeConstant.isImport) {
            importExcelData.setErrorTemplate(true);
            importExcelData.setImportMessage("正在有人进行数据导入！请稍后重试");
            return importExcelData;
        }
        CodeConstant.isImport = true;
        XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
//        获取第一个表单
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        if (sheetAt.getFirstRowNum() == -1) {
            importExcelData.setErrorTemplate(true);
            importExcelData.setImportMessage("导入excel为空！");
            return importExcelData;
        }
//        templateConfirmation方法在下面
        importExcelData.setErrorTemplate(templateConfirmation(sheetAt, importParam.getTemplateType()));
        if (importExcelData.getErrorTemplate()) {
            importExcelData.setImportMessage("导入excel与模板不相符，请核对!");
            return importExcelData;
        }
        List<String> ids = new ArrayList<>();
        // 防止空内容导致报错
        ids.add("-1");
        List<DetailCost> addList = new ArrayList<>();
        List<Integer> commonTypes = new ArrayList<>(Arrays.asList(2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15));
        if (importParam.getTemplateType() == 1) {
//            thirdParty方法在下面
            importExcelData = thirdParty(sheetAt, importParam);
            if (importExcelData.getErrorProjectList().size() == 0) {
//                把businesses里的插入到数据库中
                this.businessService.saveBatch(importExcelData.getBusinesses());
//                codes: 新加business记录里面的code -> idMap: codes中的所有code->id
                List<String> codes = new ArrayList<>();
                codes.add("-1");
                for (Business business : importExcelData.getBusinesses()) {
                    codes.add(business.getCode());
                }
                LambdaQueryWrapper<Business> q1 = new LambdaQueryWrapper<>();

                q1.in(Business::getCode, codes);
                List<Business> businessList = businessMapper.selectList(q1);
                Map<String, Long> idMap = new HashMap<>();
                for (Business business : businessList) {
                    idMap.put(business.getCode(), business.getId());
                }
//                把新增的business的数据放额外表里一份
                List<StandardSubExtra> standardSubExtraList = new ArrayList<>();
                for (Business business : importExcelData.getBusinesses()) {
                    StandardSubExtra standardSubExtra = new StandardSubExtra();
                    standardSubExtra.setProjectId(business.getProjectId());
                    standardSubExtra.setCode(business.getCode());
                    standardSubExtra.setBusinessId(idMap.get(business.getCode()));
                    standardSubExtra.setCreateTime(new Date());
                    standardSubExtra.setCreateBy(FebsUtil.getCurrentUser().getUsername());
                    standardSubExtraList.add(standardSubExtra);
                }
                this.standardSubExtraService.saveBatch(standardSubExtraList);
            }
        } else if (commonTypes.contains(importParam.getTemplateType())) {
//            commonTemplate方法在下面
            importExcelData = commonTemplate(sheets, importParam);
        }
        //  分为添加和修改
        if (importExcelData.getErrorProjectList().size() == 0) {
            addList.addAll(importExcelData.getDetailCosts());
            Set<String> idYearMonthCodes = new HashSet<>();
            List<DetailCost> list = this.findDetailCosts();
            addList.forEach(e -> idYearMonthCodes.add(e.getCompanyId() + "-" + e.getProjectId() + "-" + e.getYearMonth() + "-" + e.getDetailSubjectCode()));
            list.forEach(e -> {
                String idYearMonthCode = e.getCompanyId() + "-" + e.getProjectId() + "-" + e.getYearMonth() + "-" + e.getDetailSubjectCode();
                if (idYearMonthCodes.contains(idYearMonthCode)) {
                    ids.add(e.getId().toString());
                }
            });
        }
        if (importExcelData.getHasErrorMonth() != -1) {
            String info = "行月份错误，请核对！";
            importExcelData.setImportMessage("第".concat(importExcelData.getHasErrorMonth().toString()).concat(info));
        } else if (importExcelData.getErrorProjectList().size() > 0 || importExcelData.getErrorSubjectList().size() > 0) {
            List<Integer> errorList = importExcelData.getErrorProjectList().size() > 0 ?
                    importExcelData.getErrorProjectList() : importExcelData.getErrorSubjectList();
            StringBuilder errorRow = new StringBuilder(errorList.get(0).toString());
            for (int i = 1; i < errorList.size(); i++) {
                errorRow.append(",").append(errorList.get(i).toString());
            }
            String info = importExcelData.getErrorProjectList().size() > 0 ? "行项目或者公司有误，请核对！" : "行科目名称不存在，请核对！";
            importExcelData.setImportMessage("第".concat(errorRow.toString()).concat(info));
        } else {
            importExcelData.setImportMessage("成功导入".concat(importExcelData.getSuccessNum().toString()).concat("行数据"));
            this.removeByIds(ids);
            this.saveBatch(addList);
        }
        importExcelData.setErrorTemplate(false);
        LambdaQueryWrapper<DetailCost> delete = new LambdaQueryWrapper<>();
        delete.eq(DetailCost::getCost, 0).ge(DetailCost::getType, 1);
        detailCostMapper.delete(delete);
        CodeConstant.isImport = false;
        return importExcelData;
    }


    // 第三方支出模板导入
    private ImportExcelData thirdParty(XSSFSheet sheetAt, ImportParam importParam) {
        ImportExcelData importExcelData = new ImportExcelData();
        importExcelData.setSuccessNum(0);
        importExcelData.setHasErrorNum(false);
        List<Integer> errorProjectList = new ArrayList<>();
        List<Integer> errorSubjectList = new ArrayList<>();
        List<DetailCost> detailCosts = new ArrayList<>();
        List<Business> businessList = new ArrayList<>();
//        initialDataIndex方法在下面
        ExcelDataIndex excelDataIndex = initialDataIndex(importParam.getTemplateType());
        // projectMap: 项目的名字->id
        List<Project> projects = this.projectService.findProjects(null);
        HashMap<String, Long> projectMap = new HashMap<>();
        projects.forEach(e -> projectMap.put(e.getProjectName(), e.getId()));
        List<Company> companies = companyService.findCompanys(null);
        HashMap<String, Long> companyMap = new HashMap<>();
        companies.forEach(e -> companyMap.put(e.getCompanyName(), e.getId()));
//        codeMap: project项目id->(额外表里的名字和code（可能有多组）)
        LambdaQueryWrapper<Business> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(Business::getType, 6);
        List<Business> businesses = businessMapper.selectList(queryWrapper);
        Map<String, String> nameCodes = new HashMap<>();
        businesses.forEach(e->nameCodes.put(e.getName(),e.getCode()));
        int startRow = sheetAt.getFirstRowNum() + excelDataIndex.getStartRow();
        for (int i = startRow; i <= sheetAt.getLastRowNum(); i++) {
            XSSFRow row = sheetAt.getRow(i);
            if (row != null) {
                XSSFCell companyNameCell = row.getCell(excelDataIndex.getCompanyNameIndex());
                XSSFCell projectNameCell = row.getCell(excelDataIndex.getProjectNameIndex());
                XSSFCell subjectCell = row.getCell(excelDataIndex.getSubjectIndex());
                if (companyNameCell != null && projectNameCell != null && subjectCell != null
                        && companyNameCell.getCellType() != CellType.BLANK
                        && projectNameCell.getCellType() != CellType.BLANK
                        && subjectCell.getCellType() != CellType.BLANK) {
                    String cName = companyNameCell.getStringCellValue().trim();
                    String pName = projectNameCell.getStringCellValue().trim();
                    // 错误判断
                    if (!companyMap.containsKey(cName)){
                        errorProjectList.add(i + 1);
                        continue;
                    }
                    if (!projectMap.containsKey(pName)) {
                        errorProjectList.add(i + 1);
                        continue;
                    }
                    if (!companyProjectService.getCompanyIdsByPid(projectMap.get(pName)).contains(companyMap.get(cName))) {
                        errorProjectList.add(i + 1);
                        continue;
                    }
                    String subjectName = subjectCell.getStringCellValue().trim();
                    Long companyId = companyMap.get(cName);
                    Long projectId = projectMap.get(pName);
                    String costCode = CodeConstant.costCode; //成本主项目code
                    for (Map.Entry<Integer, Integer> entry : excelDataIndex.getMonthIndex().entrySet()) {
                        XSSFCell leftCostCell = row.getCell(entry.getValue() - 1);
                        XSSFCell costCell = row.getCell(entry.getValue());
                        double leftCost = -1;
                        double cost = -1;
                        try {
                            leftCost = leftCostCell.getNumericCellValue();
                            cost = costCell.getNumericCellValue();
                        } catch (Exception e) {
                            importExcelData.setHasErrorNum(true);
                        }
                        String month = entry.getKey() < 10 ? "0" + entry.getKey() : entry.getKey() + "";
                        month = "13".equals(month) ? "balance" : month;     // 差额当作特殊月份处理
                        String yearMonth = importParam.getYear() + "-" + month;
                        if (cost != -1) {
                            if (!nameCodes.containsKey(subjectName)) {
                                // 新的额外明细科目
                                Business business = new Business();
                                business.setName(subjectName);
                                business.setCode(UUID.randomUUID().toString().replaceAll("-", ""));
                                business.setType(6);
                                business.setFatherId(0L);
                                business.setStandardMain(costCode);
                                business.setCreateBy(FebsUtil.getCurrentUser().getUsername());
                                business.setCreateTime(new Date());
                                business.setProjectId(projectId);
                                // 更新nameCodesMap
                                nameCodes.put(subjectName,business.getCode());
                                business.setOrder(1601);
                                businessList.add(business);
                            }
                            // new detailCost
                            String remark = null;
                            XSSFComment comment = costCell.getCellComment();
                            if (comment != null) {
                                remark = costCell.getCellComment().getString().toString().trim();
                                String author = comment.getAuthor();
                                if (!author.equals("")) {
                                    remark = remark.replace(author + ":", "");
                                }
                            }
                            DetailCost detailCost = new DetailCost();
                            detailCost.setCompanyId(companyId);
                            detailCost.setProjectId(projectId);
                            detailCost.setDetailSubjectCode(nameCodes.get(subjectName));
                            detailCost.setType(6);
                            detailCost.setCost(BigDecimal.valueOf(cost));
                            detailCost.setRemark(remark);

//                    为什么Estimate直接赋零啊？？？？？？？？
                            detailCost.setEstimate(0);
                            detailCost.setYearMonth(yearMonth);
                            detailCost.setCreateTime(new Date());
                            detailCost.setIsDeleted(0);
                            detailCost.setCreateBy(FebsUtil.getCurrentUser().getUsername());
                            if (leftCost != -1) {
                                detailCost.setInputTaxAdjustment(BigDecimal.valueOf(leftCost));
                            }
                            detailCosts.add(detailCost);
                        }
                    }
                    importExcelData.setSuccessNum(importExcelData.getSuccessNum() + 1);
                }
            }
        }
        // 相同项目名称合并金额和批注
//        addList: 金额合并需要添加进表的detailCost
        List<DetailCost> addList = new ArrayList<>();
        Set<String> idCodeYearMonth = new HashSet<>();
        detailCosts.forEach(e -> {
            String key = e.getProjectId() + "-" + e.getDetailSubjectCode() + "-" + e.getYearMonth();
            if (!idCodeYearMonth.contains(key)) {
                addList.add(e);
                idCodeYearMonth.add(key);
            } else {
                addList.forEach(item -> {
                    String key1 = item.getProjectId() + "-" + item.getDetailSubjectCode() + "-" + item.getYearMonth();
                    if (key1.equals(key)) {
                        item.setCost(item.getCost().add(e.getCost()));
                        item.setInputTaxAdjustment(item.getInputTaxAdjustment().add(e.getInputTaxAdjustment()));
                        if (item.getRemark() != null && e.getRemark() != null) {
                            item.setRemark(item.getRemark() + "\n" + e.getRemark());
                        } else if (item.getRemark() == null || e.getRemark() == null) {
                            item.setRemark(item.getRemark() == null ? e.getRemark() : item.getRemark());
                        }
                    }
                });
            }
        });
        importExcelData.setErrorProjectList(errorProjectList);
        importExcelData.setErrorSubjectList(errorSubjectList);
        importExcelData.setDetailCosts(addList);
        importExcelData.setBusinesses(businessList);
        return importExcelData;
    }

    //其他类似模板导入
    private ImportExcelData commonTemplate(XSSFWorkbook sheets, ImportParam importParam) {
        ImportExcelData importExcelData = new ImportExcelData();
        importExcelData.setSuccessNum(0);
        List<Integer> errorProjectList = new ArrayList<>();
        List<Integer> errorSubjectList = new ArrayList<>();
        List<DetailCost> detailCosts = new ArrayList<>();
//        initialDataIndex方法在下面
        ExcelDataIndex excelDataIndex = initialDataIndex(importParam.getTemplateType());
        // projectMap: 所有项目的名字->id
        List<Project> projects = this.projectService.findProjects(null);
        HashMap<String, Long> projectMap = new HashMap<>();
        projects.forEach(e -> projectMap.put(e.getProjectName(), e.getId()));

        List<Company> companies = companyService.findCompanys(null);
        HashMap<String, Long> companyMap = new HashMap<>();
        companies.forEach(e -> companyMap.put(e.getCompanyName(), e.getId()));

        int sheetCount = sheets.getNumberOfSheets();
        for (int s = 0; s < sheetCount; s++) {
            XSSFSheet sheetAt = sheets.getSheetAt(s);
            int startRow = sheetAt.getFirstRowNum() + excelDataIndex.getStartRow();
            List<String> shield = Arrays.asList("合计", "待定");    // 屏蔽词
            for (int i = startRow; i <= sheetAt.getLastRowNum(); i++) {
                //System.out.println("第" + i + "行") ;
                //System.out.println("从第" + row.getFirstCellNum() + "列到第" + row.getLastCellNum() + " 列");
                XSSFRow row = sheetAt.getRow(i);
                if (row == null) continue;
                // 跳过非数据行
                boolean blankRow = true;
                for (Map.Entry<Integer, Integer> entry : excelDataIndex.getMonthIndex().entrySet()) {
                    XSSFCell cell = row.getCell(entry.getValue());
                    if (cell != null && cell.getCellType() != CellType.BLANK) {
                        blankRow = false;// 不是空的
                    }
                }
                for (int m = row.getFirstCellNum(); m < row.getLastCellNum(); m++) {
                    XSSFCell cell = row.getCell(m);
                    if (cell != null && cell.getCellType() == CellType.STRING && shield.contains(cell.getStringCellValue())) {
                        blankRow = true;
                        break;
                    }
                }
                if (blankRow) {
                    continue;
                }
                XSSFCell companyNameCell = row.getCell(excelDataIndex.getCompanyNameIndex());
                XSSFCell projectNameCell = row.getCell(excelDataIndex.getProjectNameIndex());
                String companyName = "";
                String projectName = "";
                String subjectName = "";
                try {
                    projectName = projectNameCell.getStringCellValue().trim();
                } catch (Exception e) {
                    projectName = "null";
                }
                try {
                    companyName = companyNameCell.getStringCellValue().trim();
                } catch (Exception e) {
                    companyName = "null";
                }
                // 错误判断
                if (!companyMap.containsKey(companyName)){
                    errorProjectList.add(i + 1);
                    continue;
                }
                if (!projectMap.containsKey(projectName)) {
                    errorProjectList.add(i + 1);
                    continue;
                }
                if (!companyProjectService.getCompanyIdsByPid(projectMap.get(projectName)).contains(companyMap.get(companyName))) {
                    errorProjectList.add(i + 1);
                    continue;
                }
                // 多个科目
                if (importParam.getTemplateType() == 9 || importParam.getTemplateType() == 14) {
                    XSSFCell subjectNameCell = row.getCell(excelDataIndex.getSubjectIndex());
                    try {
                        subjectName = subjectNameCell.getStringCellValue().trim();
                    } catch (Exception e) {
                        subjectName = "null";
                    }
                    if (!excelDataIndex.getNameCodeMap().containsKey(subjectName)) {
                        errorSubjectList.add(i + 1);
                        continue;
                    }
                }
                XSSFCell remarkCell = null;
                if (excelDataIndex.getRemarkIndex() != -1) {
                    remarkCell = row.getCell(excelDataIndex.getRemarkIndex());
                }
                String remark = remarkCell == null ? null : remarkCell.getStringCellValue().trim();
                Long companyId = companyMap.get(companyName);
                Long projectId = projectMap.get(projectName);
                for (Map.Entry<Integer, Integer> entry : excelDataIndex.getMonthIndex().entrySet()) {
                    XSSFCell costCell = row.getCell(entry.getValue());
//                获取每个项目科目月份的cost值
                    BigDecimal cost = new BigDecimal(-1);   //  允许设置0值
                    BigDecimal mainCost = new BigDecimal(-1);
                    try {
                        if (costCell == null || costCell.getCellType().equals(CellType.BLANK)) {
                            continue;
                        }
                        cost = BigDecimal.valueOf(costCell.getNumericCellValue());
                        if (excelDataIndex.getMainCostIndex() != 0) {
                            mainCost = BigDecimal.valueOf(row.getCell(entry.getValue() + excelDataIndex.getMainCostIndex()).getNumericCellValue());
                        }
                    } catch (Exception e) {
                        importExcelData.setHasErrorNum(true);
                    }

                    if (cost.compareTo(new BigDecimal(-1)) != 0) {
                        String month = entry.getKey() < 10 ? "0" + entry.getKey() : entry.getKey() + "";
                        if (importParam.getTemplateType() == 14) {  // 场地费特殊处理，月份全部在一列（看模板）
                            month = row.getCell(entry.getValue() + 2).getStringCellValue().trim().replace("月", "");
                            try {
                                month = "差额".equals(month) ? "13" : month;
                                month = Integer.parseInt(month) < 10 ? "0" + month : month;
                            } catch (Exception e) {
                                importExcelData.setHasErrorMonth(i + 1);
                                return importExcelData;
                            }
                        }
                        month = "13".equals(month) ? "balance" : month;     // 差额当作特殊月份处理
                        String yearMonth = importParam.getYear() + "-" + month;
                        // new DetailCost
                        DetailCost detailCost = new DetailCost();
                        detailCost.setProjectId(projectId);
                        detailCost.setCompanyId(companyId);
                        if (importParam.getTemplateType() == 9 || importParam.getTemplateType() == 14) {
                            detailCost.setDetailSubjectCode(excelDataIndex.getNameCodeMap().get(subjectName));
                        } else {
                            detailCost.setDetailSubjectCode(excelDataIndex.getCode());
                        }
                        detailCost.setType(2);
                        detailCost.setCost(cost);
                        if (mainCost.compareTo(new BigDecimal(-1)) != 0) {
                            detailCost.setInputTaxAdjustment(mainCost);
                        }
                        if (excelDataIndex.getRemarkIndex() == -1) {
                            XSSFComment comment = costCell.getCellComment();
                            if (comment != null) {
                                remark = costCell.getCellComment().getString().toString().trim();
                                String author = comment.getAuthor();
                                if (!author.equals("")) {
                                    remark = remark.replace(author + ":", "");
                                }
                            } else {
                                remark = null;
                            }
                        }
                        if (remark != null && !remark.equals("")) {
                            detailCost.setRemark(remark);
                        }
                        detailCost.setEstimate(0);
                        detailCost.setYearMonth(yearMonth);
                        detailCost.setCreateTime(new Date());
                        detailCost.setIsDeleted(0);
                        detailCost.setCreateBy(FebsUtil.getCurrentUser().getUsername());
                        detailCosts.add(detailCost);
                    }
                }
                importExcelData.setSuccessNum(importExcelData.getSuccessNum() + 1);
            }
        }

        // 相同公司+项目名称合并金额和批注, 相应detailCost存在addList内
        List<DetailCost> addList = new ArrayList<>();
        Set<String> idCodeYearMonth = new HashSet<>();
        detailCosts.forEach(e -> {
            String key = e.getCompanyId() + "-" + e.getProjectId() + "-" + e.getDetailSubjectCode() + "-" + e.getYearMonth();
            if (!idCodeYearMonth.contains(key)) {
                addList.add(e);
                idCodeYearMonth.add(key);
            } else {
                addList.forEach(item -> {
                    String key1 = item.getCompanyId() + "-" + item.getProjectId() + "-" + item.getDetailSubjectCode() + "-" + item.getYearMonth();
                    if (key1.equals(key)) {
                        item.setCost(item.getCost().add(e.getCost()));
                        if (excelDataIndex.getMainCostIndex() != 0) {
                            item.setInputTaxAdjustment(item.getInputTaxAdjustment().add(e.getInputTaxAdjustment()));
                        }
                        if (item.getRemark() != null && e.getRemark() != null) {
                            item.setRemark(item.getRemark() + "\n" + e.getRemark());
                        } else if (item.getRemark() == null || e.getRemark() == null) {
                            item.setRemark(item.getRemark() == null ? e.getRemark() : item.getRemark());
                        }
                    }
                });
            }
        });
        importExcelData.setErrorProjectList(errorProjectList);
        importExcelData.setErrorSubjectList(errorSubjectList);
        importExcelData.setDetailCosts(addList);
        return importExcelData;
    }

    // 初始化不同模板列索引
    private ExcelDataIndex initialDataIndex(Integer templateType) {
        ExcelDataIndex excelDataIndex = new ExcelDataIndex();
        LinkedHashMap<Integer, Integer> monthIndex = new LinkedHashMap<>();
        // 表数据索引
        List<Integer> commonTypes = new ArrayList<>(Arrays.asList(2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15));
        // 数据开始行
        Integer[] startRowIndex = {-1, -1, 2, 2, 1, 1, 1, 1, 2, 2, 2, 1, 1, 2, 1, 2};
        // 公司名称列
        Integer[] companyIndex = {-1, -1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0};
        // 项目名称列
        Integer[] projectIndex = {-1, -1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1};
        // 批注列（无用）
        Integer[] remarkIndex = {-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, 10, -1};
        // 科目列，单科目-1，多科目对应列
        Integer[] subjectIndex = {-1, -1, -1, -1, -1, -1, -1, -1, -1, 3, -1, -1, -1, -1, 3, -1};
        // 月份列, 值为月份开始列-1
        Integer[] monthsIndex = {-1, -1, 4, 4, 8, 3, 9, 7, 5, 6, 6, 3, 3, 7, 4, 13};
        // 月份列之间间隔列
        Integer[] monthStep =  {-1, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 0, 0, 2, 0, 2};
        // 对应增值税进项列
        Integer[] mainCostIndex = {0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, 1, -1};
        // 导入数据对应标准科目code
        String[] codeIndex = {"-1", "-1", "60a007b549fd11ecba500242ac110002", "37bb8b4049fd11ecba500242ac110002",
                "7a7df5a049fd11ecba500242ac110002", "f6c622ce49fd11ecba500242ac110002", "299be8bb4a0411ecba500242ac110002",
                "2f0eae5249fe11ecba500242ac110002", "7095c4f94a0411ecba500242ac110002", "-1", "792faf7e4a0411ecba500242ac110002",
                "055f803847834fd8bf31c2bf988789d7", "567b503a8ea342f2bd86e68a584afbc0", "5a2323ec49fc11ecba500242ac110002", "-1",
                "fbe9419249fd11ecba500242ac110002"
        };
        String[] subjectName = new String[0];
        String[] subjectCode = new String[0];
        if (templateType == 9) {
            // 多个标准科目
            subjectName = new String[]{"年终奖金预提（管理）", "每月应发工资（管理）", "社会保险费单位（管理）", "公积金单位（管理）"};
            subjectCode = new String[]{"20f2bbb64a0411ecba500242ac110002", "25c554134a0411ecba500242ac110002",
                    "299be8bb4a0411ecba500242ac110002", "2d8f04a04a0411ecba500242ac110002"};
        }
        if (templateType == 14) {
            subjectName = new String[]{"办公场地场租费（座席租赁费）", "办公场地装修费摊销", "办公场地物管费", "办公场地水电费", "办公现场宽带费", "固定资产摊销"};
            subjectCode = new String[]{CodeConstant.rentalFee, CodeConstant.decorationFee, CodeConstant.propertyManagementFee,
                    CodeConstant.waterAndElectricity, CodeConstant.broadbandFee, CodeConstant.fixedAssets};
        }
        if (templateType == 1) {
            excelDataIndex.setStartRow(2);
            excelDataIndex.setCompanyNameIndex(0);
            excelDataIndex.setProjectNameIndex(1);
            excelDataIndex.setSubjectIndex(2);
            for (int i = 1; i <= 13; i++) {
                monthIndex.put(i, 7 + 3 * (i - 1));
            }
        } else if (commonTypes.contains(templateType)) {
            // 添加对应下标
            excelDataIndex.setStartRow(startRowIndex[templateType]);
            excelDataIndex.setCompanyNameIndex(companyIndex[templateType]);
            excelDataIndex.setProjectNameIndex(projectIndex[templateType]);
            excelDataIndex.setRemarkIndex(remarkIndex[templateType]);
            excelDataIndex.setSubjectIndex(subjectIndex[templateType]);
            excelDataIndex.setMainCostIndex(mainCostIndex[templateType]);
            if (templateType == 9 || templateType == 14) {
                Map<String, String> nameCodeMap = new HashMap<>();
                for (int i = 0; i < subjectName.length; i++) {
                    nameCodeMap.put(subjectName[i], subjectCode[i]);
                }
                excelDataIndex.setNameCodeMap(nameCodeMap);
            } else {
                excelDataIndex.setCode(codeIndex[templateType]);
            }
            // 设置导入金额所在列
            if (templateType == 14) {
                monthIndex.put(-1, 5);
            } else {
                for (int i = 1; i <= 13; i++) {
                    if (i == 1) {
                        monthIndex.put(i, i + monthsIndex[templateType]);
                    } else {
                        monthIndex.put(i, i + monthsIndex[templateType] + (i - 1) * monthStep[templateType]);
                    }
                }
            }
        }
        excelDataIndex.setMonthIndex(monthIndex);
        return excelDataIndex;
    }

    // 判断excel内容是否与模板匹对
    private Boolean templateConfirmation(XSSFSheet sheetAt, Integer type) {
        LinkedHashMap<Integer, String> indexMap = new LinkedHashMap<>();
        boolean result = false;
        // 下标和对应 title内容
        Integer[] index = new Integer[0];
        String[] strings = new String[0];
        int startRowAdd = 0;
        if (type == 1) {
            index = new Integer[]{0, 1, 2, 3, 4};
            strings = new String[]{"所属公司", "所属项目", "内容", "税率", "总税率"};
        } else if (type == 2 || type == 3) {
            index = new Integer[]{0, 1, 2, 3, 4};
            strings = new String[]{"序号", "所属公司", "项目名称", "明细科目", "摘要"};
            startRowAdd = 1;
        } else if (type == 4) {
            index = new Integer[]{0, 1, 2, 3, 4, 5, 6, 7, 8};
            strings = new String[]{"序号", "所属公司", "项目归属", "摘要", "业务名称", "是否需要提供分包明细", "第三方职场", "分包对接人", "备注"};
        } else if (type == 5) {
            index = new Integer[]{0, 1, 2, 3};
            strings = new String[]{"序号", "所属公司", "项目归属", "摘要"};
        } else if (type == 6) {
            index = new Integer[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
            strings = new String[]{"序号", "所属公司", "项目归属", "项目对接人", "社保缴费基数", "社保缴费比例", "医疗缴费基数", "医疗缴费比例",
                    "服务费", "社保合计"};
        } else if (type == 7) {
            index = new Integer[]{0, 1, 2, 3, 4, 5, 6, 7};
            strings = new String[]{"序号", "所属公司", "项目名称", "明细科目", "地址", "房间", "房东", "摘要"};
        } else if (type == 8) {
            index = new Integer[]{0, 1, 2, 3, 4};
            strings = new String[]{"序号", "所属公司", "项目名称", "科目明细", "分摊比例"};
        } else if (type == 9) {
            index = new Integer[]{0, 1, 2, 3, 4, 5};
            strings = new String[]{"序号", "所属公司", "项目名称", "科目明细", "管理人员", "分摊比例"};
        } else if (type == 10) {
            index = new Integer[]{0, 1, 2, 3, 4, 5};
            strings = new String[]{"序号", "所属公司", "项目名称", "科目明细", "公司名称", "分摊比例"};
        } else if (type == 11 || type == 12) {
            index = new Integer[]{0, 1, 2, 3};
            strings = new String[]{"序号", "所属公司", "项目名称", "明细科目"};
        } else if (type == 13) {
            index = new Integer[]{0, 1, 2, 3, 4, 5};
            strings = new String[]{"序号", "所属公司", "项目名称", "明细科目", "税率", "总税率"};
        } else if (type == 14) {
            index = new Integer[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10};
            strings = new String[]{"序号", "所属公司","项目名称", "费用明细", "含税价", "本月金额（不含税）", "增值税进项税调整", "月份", "税率", "发票性质", "备注"};
        } else if (type == 15) {
            index = new Integer[]{0, 1, 10, 11};
            strings = new String[]{"所属公司", "所属项目",  "税率", "总税率"};
        }
        for (int i = 0; i < index.length; i++) {
            indexMap.put(index[i], strings[i]);
        }
        int startRow = sheetAt.getFirstRowNum() + startRowAdd;
        XSSFRow row = sheetAt.getRow(startRow);
        // 判断excel格式和模板是否一致, true为不符合
        for (int i = 0; i < row.getLastCellNum(); i++) {
            if (indexMap.containsKey(i)) {
                try {
                    if (!row.getCell(i).getStringCellValue().equals(indexMap.get(i))) {
                        result = true;
                        break;
                    }
                } catch (Exception e) {
                    result = true;
                    break;
                }
            }
        }
        return result;
    }

    ////
    // 主表导出
    @Override
    public void exportMainTable(XSSFWorkbook workbook, ExportParam exportParam) throws IOException {
        HashMap<String, XSSFFont> fontMap = StyleMap.getMainTableFontMap(workbook);
        XSSFSheet spreadsheet = workbook.createSheet("内部报表权责制");
        // 设置标题
        createTitle(spreadsheet, workbook, exportParam.getYear());
        // 设置月份和子标题
        creatSubtitle(spreadsheet, workbook, fontMap);
        // 设置主要数据
        Integer[] startIndex = creatMainData(spreadsheet, workbook, exportParam, fontMap);
        // 设置列宽
        setColWidth(spreadsheet);
        // 设置边框
        //setAllBorder(spreadsheet, workbook, 31, startIndex);
    }

    //设置标题
    private void createTitle(XSSFSheet spreadsheet, XSSFWorkbook workbook, String year) {
        XSSFRow row = spreadsheet.createRow((short) 0);
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setFontHeightInPoints((short) 18);
        fontTitle.setColor(new XSSFColor(new java.awt.Color(0, 0, 0), new DefaultIndexedColorMap()));
        fontTitle.setFontName("宋体");
        fontTitle.setBold(true);
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFont(fontTitle);
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 31);
        spreadsheet.addMergedRegion(cellRangeAddress);
        XSSFCell cell = row.createCell(0);
        // 两个公司对应不同标题
        cell.setCellValue("杭州".concat(CompanyConstant.companyName).concat("科技有限公司").concat(year).concat("年内部财务核算报表（主表）"));
        cell.setCellStyle(titleStyle);
        row.setHeightInPoints(27);
    }

    // 设置月份和子标题
    private void creatSubtitle(XSSFSheet spreadsheet, XSSFWorkbook workbook, HashMap<String, XSSFFont> fontMap) {
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setFontHeightInPoints((short) 10);
        fontTitle.setFontName("宋体");
        fontTitle.setBold(true);
        XSSFRow row1 = spreadsheet.createRow(1);
        XSSFRow row2 = spreadsheet.createRow(2);
        Integer[] index1 = {0, 1, 26, 27, 28, 29, 30};
        Integer[] fontColor1 = {0, 0, 0, 1};
        Integer[] fillColor1 = {0, 0, 0, 0};
        String[] stringValue1 = {"月份", "月份", "20*年月均数", "与全年预算差额（节约负数列示）", "全年合计", "全年合计", "全年合计"};
        Integer[] index2 = {0, 1, 26, 27, 28, 29, 30};
        Integer[] fontColor2 = {0, 0, 1, 1, 1};
        Integer[] fillColor2 = {0, 0, 1, 0, 0};
        String[] stringValue2 = {"总科目", "明细科目", "20*年月均数", "与全年预算差额（节约负数列示）",
                "实际合计（包括实际发生及预提）", "预算合计", "差额"};
        String[] fontIndex1 = {"title", "title", "title", "title", "title", "title"};
        String[] fontIndex2 = {"title", "title", "title", "title", "blueTitle", "blueTitle", "blueTitle"};
        for (int i = 0; i < index1.length; i++) {
            XSSFCellStyle style = getCellStyle(workbook, "title", stringValue1[i], 0, true,
                    fontMap, StyleMap.getFillColor);
            XSSFCell cell = row1.createCell(index1[i]);
            setCustomCell(cell, stringValue1[i], 0, style);
            ///creatCustomCell(workbook, row1, fontColor1[i], fillColor1[i], index1[i], stringValue1[i], 0);
        }
        for (int i = 0; i < index2.length; i++) {
            XSSFCellStyle style = getCellStyle(workbook, fontIndex2[i], stringValue2[i], 0, true,
                    fontMap, StyleMap.getFillColor);
            XSSFCell cell = row2.createCell(index2[i]);
            setCustomCell(cell, stringValue2[i], 0, style);
        }
        XSSFCellStyle style1 = getCellStyle(workbook, "title", "实际", 0, true,
                fontMap, StyleMap.getFillColor);
        XSSFCellStyle style2 = getCellStyle(workbook, "title", "预算", 0, true,
                fontMap, StyleMap.getFillColor);
        for (int i = 1; i <= 12; i++) {
            XSSFCell cell1 = row1.createCell(2 * i);
            XSSFCell cell11 = row1.createCell(2 * i + 1);
            XSSFCell cell2 = row2.createCell(2 * i);
            XSSFCell cell3 = row2.createCell(2 * i + 1);
            setCustomCell(cell1, i + "月", 0, style2);
            setCustomCell(cell11, i + "月", 0, style2);
            setCustomCell(cell2, "实际", 0, style1);
            setCustomCell(cell3, "预算", 0, style2);
            //creatCustomCell(workbook, row1,0 , 0, 2 * i, i + "月", 0);
            //creatCustomCell(workbook, row2, 0, 1, 2 * i, "实际", 0);
            //creatCustomCell(workbook, row2, 0,0, 2 * i + 1, "预算", 0);
            CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 1, 2 * i, 2 * i + 1);
            spreadsheet.addMergedRegion(cellRangeAddress);
        }
        Integer[] firstRow = {1, 1, 1, 1};
        Integer[] lastRow = {1, 2, 2, 1};
        Integer[] firstCol = {0, 26, 27, 28};
        Integer[] lastCol = {1, 26, 27, 30};
        for (int i = 0; i < firstRow.length; i++) {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow[i], lastRow[i], firstCol[i], lastCol[i]);
            spreadsheet.addMergedRegion(cellRangeAddress);
        }
        //锁住前三行和前两列
        spreadsheet.createFreezePane(2, 3);
    }

    // 设置主要数据
    private Integer[] creatMainData(XSSFSheet spreadsheet, XSSFWorkbook workbook, ExportParam exportParam, HashMap<String, XSSFFont> fontMap) {
        List<String> yearMonths = new ArrayList<>();
        for (int i = 1; i <= 12; i++) {
            String month = i < 10 ? "0" + i + "" : i + "";
            yearMonths.add(exportParam.getYear() + "-" + month);
        }
        // excel横向坐标
        List<String> monthList = YearMonthMapUtils.monthList;
        String az = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        List<String> colIndex = ExportUtil.getColIndex(40);
        Map<String, BigDecimal> idColCostMap = new HashMap<>();
        List<Integer> dispatchProject = new ArrayList<>();
        Map<String, EstimateHistory> estimateHistoryMap = new HashMap<>();
        List<EstimateHistory> estimateHistories = this.estimateHistoryMapper.queryByYear(exportParam.getYear());
        estimateHistories.forEach(e -> estimateHistoryMap.put(e.getKey(), e));

        List<String> ids = Arrays.asList(exportParam.getProjectId().split(","));
        // 按照所选项目排序
        List<Project> projects_tmp = this.projectService.getProjectByIds(ids);
        List<Project> projects = new ArrayList<>();
        // 将项目按照选择的顺讯排序
        if (exportParam.getOrderByPro() == 1) {
            for (String id : ids) {
                projects.add(projects_tmp.stream().filter(e->e.getId().toString().equals(id)).findFirst().orElse(null));
            }
        } else {
            projects = projects_tmp;
        }

        final int[] rowIndex = {1};
        projects.forEach(e -> {
            if (e.getTypeId().equals("2")) {    // 派遣类id
                dispatchProject.add(rowIndex[0]);
            }
            // todook 修改计算方式
//             List<SingleExportDTO> singleExportDTOs = this.queryDetailForExcel(1L, e.getId(), exportParam.getYear(), yearMonths).getSingleExportDTOS();
            List<SingleExportDTO> singleExportDTOs = detailCostUtil.getMergedSingleExport(e.getId(), exportParam.getYear(), yearMonths).getSingleExportDTOS();
            // 杭州和非杭州项目 不同算法
            String[] sub1 = {"项目年终奖金/年终活动经费预算/其他（预提）", "年终奖金预提（管理）", "每月实发工资（项目）"};
            String[] sub2 = {"项目年终奖金/年终活动经费预算/其他（预提）", "年终奖金预提（管理）", "每月应发工资（项目）",
                    "实际社会保险单位", "公积金单位（项目）"};
            String[] sub;
            if (e.getProjectCity().trim().equals("杭州")) {
                sub = sub1;
            } else {
                sub = sub2;
            }
            // 放入map,方便后面取出
            singleExportDTOs.forEach(r -> {
                String subjectTitle = "";
                switch (r.getSubName()) {
                    case "拓天项目收入": {
                        subjectTitle = "收入";
                        String key = subjectTitle + "-" + e.getId() + "-" + r.getYearMonth().split("-")[1];
                        idColCostMap.put(key, r.getCost());
                        break;
                    }
                    case "费用合计": {
                        subjectTitle = "费用";
                        String key = subjectTitle + "-" + e.getId() + "-" + r.getYearMonth().split("-")[1];
                        idColCostMap.put(key, r.getCost());
                        break;
                    }
                    case "月总人数（包括离职和新员工）": {
                        subjectTitle = "员工人数";
                        String key = subjectTitle + "-" + r.getYearMonth().split("-")[1];
                        idColCostMap.put(key, idColCostMap.getOrDefault(key, new BigDecimal("0")).add(r.getCost()));
                        break;
                    }
                }
                if (Arrays.asList(sub).contains(r.getSubName())) {
                    subjectTitle = "成本";
                    String key = subjectTitle + "-" + e.getId() + "-" + r.getYearMonth().split("-")[1];
                    idColCostMap.put(key, idColCostMap.getOrDefault(key, new BigDecimal("0")).add(r.getCost()));
                }
            });
            rowIndex[0]++;
        });
        Map<String, DetailCostDTO> mainCostMap = new HashMap<>();
        Map<String, String> nameCodeMap = new HashMap<>();
        List<DetailCostDTO> mainCosts = mainCostMapper.queryByYear(exportParam.getYear());
        mainCosts.forEach(e -> {
            String key = e.getDetailSubjectCode() + "-" + e.getYearMonth().split("-")[1];
            mainCostMap.put(key, e);
        });

        // 计算增值税进项税
        List<String> projectIds = Arrays.asList(exportParam.getProjectId().split(","));
        List<DetailCost> detailCosts = this.getBaseMapper().queryInputTaxAdjustment(projectIds, yearMonths);
        for (DetailCost e : detailCosts) {
            String key = CodeConstant.inputTaxAdjustment + "-" + e.getYearMonth().split("-")[1];
            DetailCostDTO detailCostDTO = new DetailCostDTO();
            detailCostDTO.setDetailSubjectCode(key);
            if (e.getInputTaxAdjustment() == null) e.setInputTaxAdjustment(BigDecimal.ZERO);
            detailCostDTO.setCost(e.getInputTaxAdjustment().doubleValue());
            mainCostMap.put(key, detailCostDTO);
        }

        Integer projectNum = projects.size();
        String[] subjectIndex = {"收入", "费用", "成本", "税费"};
        Integer[] mainCostNum = {0, 0, 0, 0};
        Integer[] startIndex = {3, 3, 3, 3, 3};
        Integer[] projectsIndex = {0, 0, 2, 0};
        Integer[] mainCostIndex = {0, 0, 0, 0};
        List<SubMainDetailDTO> subMainDetailDTOs = mainCostMapper.queryStandards();
        subMainDetailDTOs.forEach(e -> {
            nameCodeMap.put(e.getSubName(), e.getSubCode());
            for (int i = 0; i < 4; i++) {
                if (e.getMainName().equals(subjectIndex[i])) {
                    mainCostNum[i]++;
                    break;
                }
            }
        });
        projectsIndex[1] = mainCostNum[1];
        mainCostIndex[0] = projectNum;
        mainCostIndex[2] = projectNum + projectsIndex[2];
        for (int i = 1; i < 4; i++) {
            startIndex[i] = startIndex[i - 1] + projectNum + mainCostNum[i - 1] + 1;
        }
        startIndex[4] = startIndex[3] + mainCostNum[3] + 1;
        // 用四次循环完成，每次循环记录下次对应，项目/subject的开始下标即可,数据可以先获取
        int balanceIndex = 27; //循环内判断
        List<String> specialRow = new ArrayList<>(Arrays.asList("本部管理人员工资", "本部年终奖预提（不包括各项目管理人员）"));
        for (int i = 0; i < 4; i++) {
            // 两行特殊主表数据
            if (i == 2) {
                Integer[] fillColorIndex = {-1, 2};
                for (int j = 0; j <= 1; j++) {
                    XSSFRow row = spreadsheet.createRow(startIndex[i] + j);
                    XSSFCell cell = row.createCell(1);
                    XSSFCellStyle titleStyle = getCellStyle(workbook, "title", specialRow.get(j), 0, false,
                            fontMap, StyleMap.getFillColor);
                    setCustomCell(cell, specialRow.get(j), 0, titleStyle);
                    XSSFCellStyle style = getCellStyle(workbook, "common", specialRow.get(j), 1, false,
                            fontMap, StyleMap.getFillColor);
                    //creatCustomCell(workbook, row, 0, fillColorIndex[j], 1, specialRow.get(j), 0);
                    for (int k = 0; k < monthList.size(); k++) {
                        String key = nameCodeMap.get(specialRow.get(j)) + "-" + monthList.get(k);
                        String cost = mainCostMap.containsKey(key) ? mainCostMap.get(key).getCost().toString() : "";
                        cost = cost.equals("0") ? "" : cost;
                        String remark = mainCostMap.containsKey(key) ? mainCostMap.get(key).getRemark() : null;
                        if (k == (monthList.size() - 1)) {
                            XSSFCell cell3 = row.createCell(balanceIndex);
                            setCustomCell(cell3, cost, 1, style);
                            // creatCustomCell(workbook, row, 0, -1, balanceIndex, cost, 1);
                            if (!cost.equals("") && remark != null && !remark.trim().equals("")) {
                                setCellComment(spreadsheet, workbook, startIndex[i] + j, balanceIndex, remark);
                            }
                        } else {
                            XSSFCell cell1 = row.createCell(2 * (k + 1));
                            XSSFCell cell2 = row.createCell(2 * (k + 1) + 1);
                            // creatCustomCell(workbook, row, 0, fillColorIndex[j], 2 * (k + 1), cost, 1);
                            setCustomCell(cell1, cost, 1, style);
                            if (!cost.equals("") && remark != null && !remark.trim().equals("")) {
                                setCellComment(spreadsheet, workbook, startIndex[i] + j, 2 * (k + 1), remark);
                            }
                            String historyRemark = null;
                            if (estimateHistoryMap.containsKey(key)) {
                                cost = estimateHistoryMap.get(key).getCost().toString();
                                cost = cost.equals("0") ? "" : cost;
                                historyRemark = estimateHistoryMap.get(key).getRemark();
                            }
                            setCustomCell(cell2, cost, 1, style);
                            // creatCustomCell(workbook, row, 0, fillColorIndex[j], 2 * (k + 1) + 1, cost, 1);
                            if (historyRemark != null && !historyRemark.trim().equals("")) {
                                setCellComment(spreadsheet, workbook, startIndex[i] + j, 2 * (k + 1) + 1, historyRemark);
                            }
                        }
                    }
                }
            }
            // 项目数据
            if (i != 3) {
                for (int j = 0; j < projectNum; j++) {
                    XSSFRow row = spreadsheet.createRow(startIndex[i] + projectsIndex[i] + j);
                    XSSFCell cell = row.createCell(1);
                    XSSFCellStyle titleStyle = getCellStyle(workbook, "common", "projectName", 0, false,
                            fontMap, StyleMap.getFillColor);
                    setCustomCell(cell, projects.get(j).getProjectName(), 0, titleStyle);
                    XSSFCellStyle style = getCellStyle(workbook, "common", "null", 1, false,
                            fontMap, StyleMap.getFillColor);
                    //creatCustomCell(workbook, row, 0, 1, 1, projects.get(j).getProjectName(), 0);
                    for (int k = 0; k < monthList.size(); k++) {
                        String key = subjectIndex[i] + "-" + projects.get(j).getId() + "-" + monthList.get(k);
                        String cost = idColCostMap.containsKey(key) ? idColCostMap.get(key).toString() : "";
                        cost = cost.equals("0") ? "" : cost;
                        if (k == (monthList.size() - 1)) {
                            XSSFCell cell3 = row.createCell(balanceIndex);
                            setCustomCell(cell3, cost, 1, style);
                            // creatCustomCell(workbook, row, 0, -1, balanceIndex, cost, 1);
                        } else {
                            XSSFCell cell1 = row.createCell(2 * (k + 1));
                            XSSFCell cell2 = row.createCell(2 * (k + 1) + 1);
                            // creatCustomCell(workbook, row, 0, fillColorIndex[j], 2 * (k + 1), cost, 1);
                            setCustomCell(cell1, cost, 1, style);
                            if (estimateHistoryMap.containsKey(key)) {
                                cost = estimateHistoryMap.get(key).getCost().toString();
                            }
                            setCustomCell(cell2, cost, 1, style);
                            // creatCustomCell(workbook, row, 0, fillColorIndex[j], 2 * (k + 1) + 1, cost, 1);
                        }
                    }

                }
            }
            int cnt = 0;
            // 主表数据
            for (SubMainDetailDTO e : subMainDetailDTOs) {
                if (e.getMainName().equals(subjectIndex[i]) && !specialRow.contains(e.getSubName())) {
                    XSSFRow row = spreadsheet.createRow(startIndex[i] + mainCostIndex[i] + cnt);
                    XSSFCell cell = row.createCell(1);
                    XSSFCellStyle titleStyle = getCellStyle(workbook, "common", e.getSubName(), 0, false,
                            fontMap, StyleMap.getFillColor);
                    setCustomCell(cell, e.getSubName(), 0, titleStyle);
                    XSSFCellStyle style = getCellStyle(workbook, "common", e.getSubName(), 1, false,
                            fontMap, StyleMap.getFillColor);
                    XSSFCellStyle redFontStyle = getCellStyle(workbook, "redCommon", e.getSubName(), 1, false,
                            fontMap, StyleMap.getFillColor);
                    //creatCustomCell(workbook, row, 0, -1, 1, e.getSubName(), 0);
                    for (int k = 0; k < monthList.size(); k++) {
                        String key = e.getSubCode() + "-" + monthList.get(k);
                        String cost = mainCostMap.containsKey(key) ? mainCostMap.get(key).getCost().toString() : "";
                        cost = cost.equals("0") ? "" : cost;
                        String remark = mainCostMap.containsKey(key) ? mainCostMap.get(key).getRemark() : null;
                        String estimate = mainCostMap.containsKey(key) ? mainCostMap.get(key).getEstimate() : null;
                        if (k == (monthList.size() - 1)) {
                            XSSFCell cell3 = row.createCell(balanceIndex);
                            if (estimate != null && estimate.equals("1")) {
                                setCustomCell(cell3, cost, 1, redFontStyle);
                            } else {
                                setCustomCell(cell3, cost, 1, style);
                            }
                            // creatCustomCell(workbook, row, 0, -1, balanceIndex, cost, 1);
                            if (!cost.equals("") && remark != null && !remark.trim().equals("")) {
                                setCellComment(spreadsheet, workbook, startIndex[i] + mainCostIndex[i] + cnt, balanceIndex, remark);
                            }
                        } else {
                            XSSFCell cell1 = row.createCell(2 * (k + 1));
                            XSSFCell cell2 = row.createCell(2 * (k + 1) + 1);
                            // creatCustomCell(workbook, row, 0, fillColorIndex[j], 2 * (k + 1), cost, 1);
                            if (estimate != null && estimate.equals("1")) {
                                setCustomCell(cell1, cost, 1, redFontStyle);
                            } else {
                                setCustomCell(cell1, cost, 1, style);
                            }
                            if (!cost.equals("") && remark != null && !remark.trim().equals("")) {
                                setCellComment(spreadsheet, workbook, startIndex[i] + mainCostIndex[i] + cnt, 2 * (k + 1), remark);
                            }
                            String historyRemark = null;
                            if (estimateHistoryMap.containsKey(key)) {
                                cost = estimateHistoryMap.get(key).getCost().toString();
                                cost = cost.equals("0") ? "" : cost;
                                historyRemark = estimateHistoryMap.get(key).getRemark();
                            }
                            setCustomCell(cell2, cost, 1, style);
                            // creatCustomCell(workbook, row, 0, fillColorIndex[j], 2 * (k + 1) + 1, cost, 1);
                            if (historyRemark != null && !historyRemark.trim().equals("")) {
                                setCellComment(spreadsheet, workbook, startIndex[i] + mainCostIndex[i] + cnt, 2 * (k + 1) + 1, historyRemark);
                            }
                        }
                    }
                    cnt++;
                }
            }
            XSSFRow row = spreadsheet.createRow(startIndex[i + 1] - 1);
            XSSFCellStyle titleStyle = getCellStyle(workbook, "title", "formula", 0, false,
                    fontMap, StyleMap.getFillColor);
            XSSFCell subTitle = row.createCell(1);
            setCustomCell(subTitle, subjectIndex[i] + "合计", 0, titleStyle);
            //creatCustomCell(workbook, row, 0, 1, 1, subjectIndex[i] + "合计", 0);
            // 添加左侧标题
            XSSFRow subjectRow = spreadsheet.getRow(startIndex[i]);
            XSSFCell title = subjectRow.createCell(0);
            titleStyle = getCellStyle(workbook, "title", subjectIndex[i], 0, true,
                    fontMap, StyleMap.getFillColor);
            setCustomCell(title, subjectIndex[i], 0, titleStyle);
            //creatCustomCell(workbook, subjectRow, 0, -1, 0, subjectIndex[i], 0);
        }
        // 合并左侧标题
        for (int i = 0; i < 4; i++) {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(startIndex[i], startIndex[i + 1] - 1, 0, 0);
            spreadsheet.addMergedRegion(cellRangeAddress);
        }
        // 合计公式添加
        for (int i = 0; i < 4; i++) {
            XSSFRow row = spreadsheet.getRow(startIndex[i + 1] - 1);
            XSSFCellStyle style = getCellStyle(workbook, "common", row.getCell(1).getStringCellValue(), 3, false,
                    fontMap, StyleMap.getFillColor);
            for (int j = 2; j <= 27; j++) {
                XSSFCell cell = row.createCell(j);
                String index1 = colIndex.get(j) + ((startIndex[i] + 1) + "");
                String index2 = colIndex.get(j) + ((startIndex[i + 1] - 1) + "");
                String formula = "SUM(" + index1 + ":" + index2 + ")";
                setCustomCell(cell, formula, 3, style);
                //creatCustomCell(workbook, row, 0, 1, j, formula, 3);
            }
        }
        // 添加其他行
        String[] otherSubject = {"进项税额", "税费暂估（包括在关联公司已交税金）", "毛利润", "利润总额", "所得税", "净利润", "净利润率",
                "员工人数"};
        Integer[] otherColIndex = {1, 1, 0, 0, 0, 0, 0, 0};
        Integer[] otherFill = {-1, -1, 0, -1, 0, -1, 0, -1};
        String[] otherSubjectTitle = {"进项", "暂估"};
        String[] inputName = {"进项税额", "所得税"};
        for (int i = 0; i < otherSubject.length; i++) {
            XSSFCellStyle titleStyle1 = getCellStyle(workbook, "title", otherSubject[i], 0, true,
                    fontMap, StyleMap.getFillColor);
            XSSFCellStyle titleStyle2 = getCellStyle(workbook, "title", otherSubject[i], 0, false,
                    fontMap, StyleMap.getFillColor);
            XSSFRow row = spreadsheet.createRow(startIndex[4] + i);
            XSSFCell titleCell = row.createCell(otherColIndex[i]);
            //creatCustomCell(workbook, row, 0, otherFill[i], otherColIndex[i], otherSubject[i], 0);
            if (i < 2) {
                setCustomCell(titleCell, otherSubject[i], 0, titleStyle2);
                XSSFCell subTitleCell = row.createCell(0);
                setCustomCell(subTitleCell, otherSubjectTitle[i], 0, titleStyle1);
                //creatCustomCell(workbook, row, 0, -1, 0, otherSubjectTitle[i], 0);
            } else {
                setCustomCell(titleCell, otherSubject[i], 0, titleStyle1);
                XSSFCell subTitleCell = row.createCell(1);
                setCustomCell(subTitleCell, otherSubject[i], 0, titleStyle1);
                CellRangeAddress cellRangeAddress = new CellRangeAddress(startIndex[4] + i, startIndex[4] + i, 0, 1);
                spreadsheet.addMergedRegion(cellRangeAddress);
            }
            XSSFCellStyle style = getCellStyle(workbook, "common", otherSubject[i], 1, false,
                    fontMap, StyleMap.getFillColor);
            XSSFCellStyle redFontStyle = getCellStyle(workbook, "redCommon", otherSubject[i], 1, false,
                    fontMap, StyleMap.getFillColor);
            if (Arrays.asList(inputName).contains(otherSubject[i])) {
                for (int k = 0; k < monthList.size(); k++) {
                    String key = nameCodeMap.get(otherSubject[i]) + "-" + monthList.get(k);
                    String cost = mainCostMap.containsKey(key) ? mainCostMap.get(key).getCost().toString() : "0.00";
                    cost = cost.equals("0") ? "" : cost;
                    String remark = mainCostMap.containsKey(key) ? mainCostMap.get(key).getRemark() : null;
                    String estimate = mainCostMap.containsKey(key) ? mainCostMap.get(key).getEstimate() : null;
                    if (k == (monthList.size() - 1)) {
                        XSSFCell cell3 = row.createCell(balanceIndex);
                        if (estimate != null) {
                            setCustomCell(cell3, cost, 1, redFontStyle);
                        } else {
                            setCustomCell(cell3, cost, 1, style);
                        }
                        // creatCustomCell(workbook, row, 0, -1, balanceIndex, cost, 1);
                        if (!cost.equals("") && remark != null && !remark.trim().equals("")) {
                            setCellComment(spreadsheet, workbook, startIndex[4] + i, balanceIndex, remark);
                        }
                    } else {
                        XSSFCell cell1 = row.createCell(2 * (k + 1));
                        XSSFCell cell2 = row.createCell(2 * (k + 1) + 1);
                        // creatCustomCell(workbook, row, 0, fillColorIndex[j], 2 * (k + 1), cost, 1);
                        if (estimate != null) {
                            setCustomCell(cell1, cost, 1, redFontStyle);
                        } else {
                            setCustomCell(cell1, cost, 1, style);
                        }
                        if (!cost.equals("") && remark != null && !remark.trim().equals("")) {
                            setCellComment(spreadsheet, workbook, startIndex[4] + i, 2 * (k + 1), remark);
                        }
                        String historyRemark = null;
                        if (estimateHistoryMap.containsKey(key)) {
                            cost = estimateHistoryMap.get(key).getCost().toString();
                            cost = cost.equals("0") ? "" : cost;
                            historyRemark = estimateHistoryMap.get(key).getRemark();
                        }
                        setCustomCell(cell2, cost, 1, style);
                        // creatCustomCell(workbook, row, 0, fillColorIndex[j], 2 * (k + 1) + 1, cost, 1);
                        if (historyRemark != null && !historyRemark.trim().equals("")) {
                            setCellComment(spreadsheet, workbook, startIndex[4] + i, 2 * (k + 1) + 1, historyRemark);
                        }
                    }
                }
            } else if (otherSubject[i].equals("员工人数")) {
                for (int k = 0; k < monthList.size(); k++) {
                    String key = otherSubject[i] + "-" + monthList.get(k);
                    String cost = idColCostMap.containsKey(key) ? idColCostMap.get(key).intValue() + "" : "0";
                    if (k == (monthList.size() - 1)) {
                        XSSFCell cell3 = row.createCell(balanceIndex);
                        setCustomCell(cell3, cost, 1, style);
                        //creatCustomCell(workbook, row, 0, -1, balanceIndex, cost, 1);
                    } else {
                        XSSFCell cell1 = row.createCell(2 * (k + 1));
                        XSSFCell cell2 = row.createCell(2 * (k + 1) + 1);
                        // creatCustomCell(workbook, row, 0, fillColorIndex[j], 2 * (k + 1), cost, 1);
                        setCustomCell(cell1, cost, 1, style);
                        //creatCustomCell(workbook, row, 0, otherFill[i], 2 * (k + 1), cost, 0);
                        if (estimateHistoryMap.containsKey(key)) {
                            cost = estimateHistoryMap.get(key).getCost().intValue() + "";
                        }
                        setCustomCell(cell2, cost, 1, style);
                        //creatCustomCell(workbook, row, 0, otherFill[i], 2 * (k + 1) + 1, cost, 0);
                    }
                }
            } else {
                String formula = "";
                XSSFCellStyle formulaStyle1 = getCellStyle(workbook, "common", otherSubject[i], 3, false,
                        fontMap, StyleMap.getFillColor);
                XSSFCellStyle formulaStyle2 = getCellStyle(workbook, "common", otherSubject[i], 4, false,
                        fontMap, StyleMap.getFillColor);
                switch (otherSubject[i]) {
                    case "税费暂估（包括在关联公司已交税金）":
                        String allSum = "SUM(" + "colIndex" + (startIndex[0] + 1) + ":" + "colIndex" + (startIndex[0] + projectNum) + ")";
                        final String[] str = {""};
                        dispatchProject.forEach(dp -> {
                            str[0] += "-" + "colIndex" + (startIndex[0] + dp);
                        });
                        String expectSum = "(" + allSum + str[0] + ")";
                        formula = "expectSum/1.06*6%+(expectSum/1.06-" + "colIndex" + (startIndex[4] + 1)
                                + ")*6%*12%+allSum*80%*0.0003-(" + "colIndex" + (startIndex[3] + 1) + "+colIndex"
                                + (startIndex[3] + 2) + ")-" + "colIndex" + (startIndex[4] + 1);
                        formula = formula.replaceAll("allSum", allSum).replaceAll("expectSum", expectSum);
                        break;
                    case "毛利润":
                        formula = "colIndex" + (startIndex[1]) + "-colIndex" + (startIndex[3]);
                        break;
                    case "利润总额":
                        formula = "colIndex" + (startIndex[1]) + "-colIndex" + (startIndex[2]) + "-colIndex" +
                                (startIndex[3]) + "-colIndex" + (startIndex[4]) + "-colIndex" + (startIndex[4] + 2);
                        break;
                    case "净利润":
                        formula = "colIndex" + (startIndex[4] + 4) + "-colIndex" + (startIndex[4] + 5);
                        break;
                    case "净利润率":
                        formula = "colIndex" + (startIndex[4] + 6) + "/" + "colIndex" + (startIndex[1]);
                        break;
                }
                for (int j = 2; j <= 27; j++) {
                    String formulaString = formula.replaceAll("colIndex", colIndex.get(j));
                    XSSFCell cell = row.createCell(j);
                    if (otherSubject[i].equals("净利润率")) {
                        setCustomCell(cell, formulaString, 4, formulaStyle2);
                    } else {
                        setCustomCell(cell, formulaString, 3, formulaStyle1);
                    }
                    //creatCustomCell(workbook, row, 0, otherFill[i], j, formulaString, valueType);
                }
            }
        }
        // 额外列计算公式添加
        String[] otherColTitle = {"20*年月均数", "实际合计（包括实际发生及预提）", "预算合计", "差额"};
        Integer[] otherCol = {26, 28, 29, 30};
        for (int i = 0; i < otherCol.length; i++) {
            for (int j = startIndex[0]; j <= startIndex[4] + 7; j++) {
                XSSFCellStyle formulaStyle = getCellStyle(workbook, "common", spreadsheet.getRow(j).getCell(1).getStringCellValue(),
                        3, false, fontMap, StyleMap.getFillColor);
                int fillColor = -1;
                StringBuilder formula = new StringBuilder();
                switch (otherColTitle[i]) {
                    case "20*年月均数":
                        formula = new StringBuilder(colIndex.get(otherCol[i + 1]) + (j + 1) + "/12");
                        break;
                    case "实际合计（包括实际发生及预提）":
                        for (int k = 1; k <= 12; k++) {
                            if (k > 1) {
                                formula.append("+");
                            }
                            formula.append(colIndex.get(2 * k)).append(j + 1);
                        }
                        break;
                    case "预算合计":
                        for (int k = 1; k <= 12; k++) {
                            if (k > 1) {
                                formula.append("+");
                            }
                            formula.append(colIndex.get(2 * k + 1)).append(j + 1);
                        }
                        break;
                    case "差额":
                        formula = new StringBuilder(colIndex.get(otherCol[i - 2]) + (j + 1) + "-" + colIndex.get(otherCol[i - 1]) + (j + 1));
                        break;
                }
                XSSFRow row = spreadsheet.getRow(j);
                XSSFCell cell = row.createCell(otherCol[i]);
                setCustomCell(cell, formula.toString(), 3, formulaStyle);
                //creatCustomCell(workbook, row, 0, fillColor, otherCol[i], formula.toString(), 3);
            }
        }
        return startIndex;
    }

    /*
     * @Author HBH
     * @Description 获取单元格的样式，传入填充颜色,字体map和对应下标，添加边框
     * @Date 2022/3/27
     **/
    private XSSFCellStyle getCellStyle(XSSFWorkbook workbook, String fontType, String fillColorIndex, Integer valueType,
                                       Boolean ifCenter, HashMap<String, XSSFFont> fontMap, Map<String, java.awt.Color> fillColorMap) {
        XSSFDataFormat df = workbook.createDataFormat();
        XSSFFont font = fontMap.get(fontType);
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        if (ifCenter) {
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        }
        // 金额及公式金额 格式
        if (valueType == 1 || valueType == 3) {
            cellStyle.setDataFormat(df.getFormat("#,##0.00"));
        } else if (valueType == 4) {
            // 百分比格式
            cellStyle.setDataFormat(df.getFormat("0.00%"));
        }
        if (fillColorMap.containsKey(fillColorIndex)) {
            XSSFColor xssfColor = new XSSFColor(fillColorMap.get(fillColorIndex));
            cellStyle.setFillForegroundColor(xssfColor);
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        return cellStyle;
    }

    /*
     * @Author HBH
     * @Description 设置单元格的值，valueType：1.金额，3公式，4.百分比
     * @Date 2022/3/27
     **/
    private void setCustomCell(XSSFCell cell, String cellString, Integer valueType, XSSFCellStyle style) {
        cell.setCellStyle(style);
        if (valueType == 1) {
            cell.setCellType(CellType.NUMERIC);
            if (!cellString.equals("") && !cellString.equals("0")) {
                cell.setCellValue(Double.parseDouble(cellString));
            }
        } else if (valueType == 3 || valueType == 4) {
            cell.setCellFormula(cellString);
        } else {
            cell.setCellValue(cellString);
        }
    }

    // 设置列宽
    private void setColWidth(XSSFSheet spreadsheet) {
        spreadsheet.setColumnWidth(0, 252 * 6 + 323);
        spreadsheet.setColumnWidth(1, 252 * 30 + 323);
        for (int i = 2; i < 28; i++) {
            spreadsheet.setColumnWidth(i, 252 * 15 + 323);
        }
        for (int i = 28; i < 31; i++) {
            spreadsheet.setColumnWidth(i, 252 * 17 + 323);
        }
        XSSFRow row = spreadsheet.getRow(2);
        row.setHeightInPoints(30);
    }

    //  添加批注
    private void setCellComment(XSSFSheet sheet, XSSFWorkbook workbook, Integer rowIndex, Integer columnIndex, String commentStr) {
        XSSFDrawing draw = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置
        XSSFComment comment = draw.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, 4, 2, 9, 15));
        XSSFRichTextString rtf = new XSSFRichTextString(commentStr);
        XSSFFont commentFormatter = workbook.createFont();
        commentFormatter.setFontName("宋体");
        //设置字体大小
        commentFormatter.setFontHeightInPoints((short) 9);
        rtf.applyFont(commentFormatter);
        comment.setString(rtf);
        //comment.setAuthor("Author");
        // 将批注添加到单元格对象中'
        sheet.getRow(rowIndex).getCell(columnIndex).setCellComment(comment);
    }
}